USE master;
GO

-- If PhoneDb Database exists, delete/remove PhoneDb Database
IF DB_ID('PhoneDb') IS NOT NULL
	DROP DATABASE PhoneDb;
GO

-- Create PhoneDb Database
CREATE DATABASE PhoneDb;
GO

USE PhoneDb;
GO

-- CREATE TABLES in PhoneDb

CREATE TABLE dbo.Departments
(
	DepartmentId	int				NOT NULL	IDENTITY (1, 1),
	Name			varchar (50)	NOT NULL,
	CONSTRAINT PK_Departments
		PRIMARY KEY (DepartmentId)
);
GO

CREATE TABLE dbo.Users
(
	UserId 			int 			NOT NULL 	IDENTITY (1, 1),
	Name 			varchar (50) 	NOT NULL,
	DepartmentId	int				NOT NULL,
	CONSTRAINT PK_Users
		PRIMARY KEY (UserId),
	CONSTRAINT FK_Departments
		FOREIGN KEY (DepartmentId)
		REFERENCES dbo.Departments (DepartmentId)
		ON DELETE CASCADE
);
GO

CREATE TABLE dbo.Phones
(
	UserId			int 			NOT NULL,
	Phone			varchar(10)		NOT NULL,
	CONSTRAINT PK_Phones
		PRIMARY KEY (Phone),
	CONSTRAINT CK_PhoneLength
		CHECK ( len(Phone) = 10),
	CONSTRAINT CK_IsPhoneNumber
		CHECK ( ISNUMERIC(Phone) = 1 ),
	CONSTRAINT FK_Users
		FOREIGN KEY (UserId)
		REFERENCES dbo.Users (UserId)
		ON DELETE CASCADE
);
GO

-- Insert Data

INSERT INTO dbo.Departments (Name) VALUES ('Air');
INSERT INTO dbo.Departments (Name) VALUES ('Earth');
INSERT INTO dbo.Departments (Name) VALUES ('Element');
INSERT INTO dbo.Departments (Name) VALUES ('Fire');
INSERT INTO dbo.Departments (Name) VALUES ('Water');
GO

INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Aang', 	 1);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Appa', 	 1);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Gyatso', 1);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Momo',   1);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Toph',   2);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Bumi',   2);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Suki',   3);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Jet',    3);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Azula',  4);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Iroh',   4);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Zuko',   4);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Katara', 5);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Sokka',  5);
INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Yue', 	 5);
GO

INSERT INTO dbo.Phones VALUES ( 1, '0391111119');
INSERT INTO dbo.Phones VALUES ( 1, '0391111111');
INSERT INTO dbo.Phones VALUES ( 1, '0391111166');
INSERT INTO dbo.Phones VALUES ( 1, '0392222222');
INSERT INTO dbo.Phones VALUES ( 1, '0393333333');
INSERT INTO dbo.Phones VALUES ( 2, '0291111111');
INSERT INTO dbo.Phones VALUES ( 2, '0291111122');
INSERT INTO dbo.Phones VALUES ( 3, '0292222222');
INSERT INTO dbo.Phones VALUES ( 3, '0292222233');
INSERT INTO dbo.Phones VALUES ( 4, '0293333333');
INSERT INTO dbo.Phones VALUES ( 5, '0721111111');
INSERT INTO dbo.Phones VALUES ( 6, '0722222222');
INSERT INTO dbo.Phones VALUES ( 7, '0776111111');
INSERT INTO dbo.Phones VALUES ( 8, '0776222222');
INSERT INTO dbo.Phones VALUES ( 9, '0381111111');
INSERT INTO dbo.Phones VALUES ( 9, '0381111122');
INSERT INTO dbo.Phones VALUES (10, '0382222222');
INSERT INTO dbo.Phones VALUES (10, '0382222233');
INSERT INTO dbo.Phones VALUES (11, '0383333322');
INSERT INTO dbo.Phones VALUES (11, '0383333333');
INSERT INTO dbo.Phones VALUES (12, '0892111111');
INSERT INTO dbo.Phones VALUES (12, '0892111122');
INSERT INTO dbo.Phones VALUES (13, '0892222222');
INSERT INTO dbo.Phones VALUES (13, '0892222211');
INSERT INTO dbo.Phones VALUES (14, '0892333333');
INSERT INTO dbo.Phones VALUES (14, '0892333311');
GO

					----------------
					-- Encryption --
					----------------
					
--If there is no master key, create one now. 
IF NOT EXISTS 
	(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
	CREATE MASTER KEY 
	ENCRYPTION BY PASSWORD = 'Pa$sword';
GO

-- Create server certificate
CREATE CERTIFICATE PhoneCertificate
   WITH SUBJECT = 'Encrypted Phone Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
	WITH ALGORITHM = AES_256
	ENCRYPTION BY CERTIFICATE PhoneCertificate;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE dbo.Phones
	ADD EncryptedPhone varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC 
	KEY SSN_Key_01 DECRYPTION 
	BY CERTIFICATE PhoneCertificate;

-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE dbo.Phones
	SET EncryptedPhone = EncryptByKey(Key_GUID('SSN_Key_01'), Phone);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC 
	KEY SSN_Key_01 DECRYPTION 
	BY CERTIFICATE PhoneCertificate;
GO

-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT Phone, EncryptedPhone
	AS 'Encrypted Phone Number',
	CONVERT(varchar, DecryptByKey(EncryptedPhone)) 
	AS 'Decrypted Phone Number'
	FROM dbo.Phones;
GO

-- Create View
CREATE VIEW PhonesOwnedByUser
	AS
		SELECT Users.UserId, 
			   Users.Name 				AS Name, 
			   Departments.DepartmentId,
			   Departments.Name 		AS Department, 
			   Phone,
			   EncryptedPhone
		FROM   [PhoneDb].[dbo].[Users] 			AS Users
		JOIN   [PhoneDb].[dbo].[Departments]	AS Departments
			ON Users.DepartmentId = Departments.DepartmentId
		JOIN   [PhoneDb].[dbo].[Phones] 		AS Phones
			ON Users.UserId = Phones.UserId;
GO

-----------------------------------
-- DEPARTMENTS' STORED PROCEDURE --
-----------------------------------
CREATE PROCEDURE GetAllDepartments
	AS
		SELECT * 
		FROM [PhoneDb].[dbo].[Departments];
GO

CREATE PROCEDURE GetDepartmentById
		@DepartmentId varchar(10)
	AS
	BEGIN
		SELECT *
		FROM [PhoneDb].[dbo].[Departments]
		WHERE Departments.DepartmentId = CAST(@DepartmentId as int);
	END;
GO

CREATE PROCEDURE InsertDepartment
		@DepartmentName varchar(50)
	AS
		INSERT INTO dbo.Departments (Name) 
		VALUES (@DepartmentName);
GO

------------------------------
-- PHONES' STORED PROCEDURE --
------------------------------
CREATE PROCEDURE GetPlainPhone
		@PhoneNumber varchar(10)
	AS		
		SELECT *
		FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
		WHERE Phone LIKE '%' + @PhoneNumber + '%'
		ORDER BY Department, UserId;
GO

CREATE PROCEDURE GetEncryptedPhone
		@PlainPhoneNumber varchar(10)
	AS
	BEGIN		
		OPEN SYMMETRIC KEY SSN_Key_01 
			DECRYPTION BY CERTIFICATE PhoneCertificate;
		
		SELECT * 
		FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
		WHERE CONVERT(varchar, DecryptByKey(EncryptedPhone)) LIKE '%' + @PlainPhoneNumber + '%'
		ORDER BY Department, UserId;
		
		CLOSE SYMMETRIC KEY SSN_Key_01;
	END;
GO

CREATE PROCEDURE InsertNewPhone
		@UserId varchar(10),
		@Phone  varchar(10)
	AS
	BEGIN
		OPEN SYMMETRIC KEY SSN_Key_01 
			DECRYPTION BY CERTIFICATE PhoneCertificate;
			
		INSERT INTO [PhoneDb].[dbo].[Phones]
		VALUES ( @UserId, @Phone, EncryptByKey(Key_GUID('SSN_Key_01'), @Phone));
		
		CLOSE SYMMETRIC KEY SSN_Key_01;
	END;
GO

CREATE PROCEDURE UpdatePhoneNumber
		@OldPhoneNumber varchar(10),
		@NewPhoneNumber varchar(10)
	AS
	BEGIN
		OPEN SYMMETRIC KEY SSN_Key_01 
		DECRYPTION BY CERTIFICATE PhoneCertificate;
			
		UPDATE [PhoneDb].[dbo].[Phones]
		SET   Phone 		   = @NewPhoneNumber,
			  EncryptedPhone   = EncryptByKey(Key_GUID('SSN_Key_01'), @NewPhoneNumber)
		WHERE Phone = @OldPhoneNumber;
		
		CLOSE SYMMETRIC KEY SSN_Key_01;
	END;
GO

CREATE PROCEDURE DeletePhoneNumber
		@PlainPhoneNumber varchar(10)
	AS
	BEGIN
		DELETE FROM [PhoneDb].[dbo].[Phones]
		WHERE Phone = @PlainPhoneNumber;
	END;
GO

------------------------------
-- USERS' STORED PROCEDURE --
------------------------------
CREATE PROCEDURE GetAllUsers
	AS
		SELECT * 
		FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
		ORDER BY Department, UserId;
GO

CREATE PROCEDURE GetUserByName
		@Name varchar(50)
	AS
	BEGIN
		SELECT * 
		FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
		WHERE Name LIKE '%' + @Name + '%'
		ORDER BY Department, UserId;	
	END;
GO

CREATE PROCEDURE GetUserByDepartment
		@Department varchar(50)
	AS
	BEGIN
		SELECT * 
		FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
		WHERE Department = @Department
		ORDER BY Department, UserId;	
	END;
GO

CREATE PROCEDURE InsertUser
		@Name 			  varchar(50),
		@Department 	  varchar(50),
		@PlainPhoneNumber varchar(10)
	AS
	BEGIN
		DECLARE @LastUserId int;
		
		INSERT INTO [PhoneDb].[dbo].[Users] (Name, DepartmentId)
			SELECT @Name, DepartmentId
			FROM [PhoneDb].[dbo].[Departments]
			WHERE Name = @Department;
		
		EXEC GetLastInsertId '[PhoneDb].[dbo].[Users]', @LastUserId OUTPUT
		EXEC InsertNewPhone @LastUserId, @PlainPhoneNumber;
	END;
GO

CREATE PROCEDURE UpdateUser
		@UserId int,
		@Name	varchar(50)
	AS
	BEGIN
		UPDATE [PhoneDb].[dbo].[Users]
		SET   Name   = @Name
		WHERE UserId = @UserId;			
	END;
GO

CREATE PROCEDURE DeleteUser
		@UserId int
	AS
	BEGIN
		DELETE FROM [PhoneDb].[dbo].[Users]
		WHERE UserId = @UserId;
	END;
GO

----------------------------------------
-- General PhoneDb's Stored Procedure --
----------------------------------------
CREATE PROCEDURE GetLastInsertId
		@TableName varchar(50),
		@LastId    int 			OUTPUT
	AS
	BEGIN
		SELECT @LastId = IDENT_CURRENT (@TableName);
	END;
GO